<?php

namespace Wgx\PdoEx;


class PDOEx extends \PDO
{
    const SqlDateTimeFormat = 'Y-m-d H:i:s';

    const LOGICAL_AND = '__AND__';
    const LOGICAL_OR = '__OR__';

    /** @var bool */
    public $dryRun = false;
    /** @var string */
    private $sql = null;
    /** @var array|null */
    private $params = null;

    /**
     * 这个回调函数会在执行sql前调用
     * 回调函数原型：void func(string $sql, mixed $sqlParameters)
     *
     * @var null|mixed
     */
    public $preExecuteSqlCallback = null;

    /** @var bool */
    private $isOk = true;
    /** @var null|\PDOStatement */
    private $pdoStatement = null;
    /** @var bool */
    private $getAllHasCalled = false;

    /**
     * @param null|string $name - $name 为 empty 时默认 get default pdo
     * @return null|PDOEx
     * @throws \Exception
     */
    public static function getPdoObj($name = null)
    {
        static $name2PdoObjArr = array();

        if (empty($name)) {
            $name = self::getDefaultPdoName();
            if (empty($name)) {
                return null;
            }
        }

        if (!isset($name2PdoObjArr[$name])) {
            $config = self::getPdoConfig($name);
            if (!$config) {
                throw new \Exception('pdo config error: there is not pdo config ' . $name, 500);
            }

            $pdo = new static($config['dsn'], $config['user'], $config['password']);
            $name2PdoObjArr[$name] = $pdo;
        }

        return $name2PdoObjArr[$name];
    }

    /**
     * @return null|string
     */
    protected static function getDefaultPdoName()
    {
        static $defaultPdoName = null;
        if (!$defaultPdoName) {
            $allConfig = self::getPdoConfig();
            if (empty($allConfig)) {
                return null;
            }

            foreach ($allConfig as $n => $c) {
                if (is_array($c) && !empty($c['default'])) {
                    $defaultPdoName = $n;
                    break;
                }
            }

            if (empty($defaultPdoName)) {
                if (!empty($allConfig['default'])) {
                    if (is_string($allConfig['default'])) {
                        $defaultPdoName = $allConfig['default'];
                    } else {
                        $defaultPdoName = 'default';
                    }
                } else {
                    $defaultPdoName = array_keys($allConfig)[0];
                }
            }
        }

        return $defaultPdoName;
    }

    /**
     * @param null|string $name
     * @return mixed|null
     */
    public static function getPdoConfig($name = null)
    {
        static $pdoConfig = null;
        if (!$pdoConfig) {
            $pdoConfig = require BASE_DIR . '/config/pdo.php';
        }
        if (empty($pdoConfig)) {
            return null;
        }

        if (empty($name)) {
            return $pdoConfig;
        } else {
            if (empty($pdoConfig[$name])) {
                return null;
            }

            return $pdoConfig[$name];
        }
    }

    /**
     * @return string
     */
    public function getSql()
    {
        return $this->sql;
    }

    /**
     * @return array|null
     */
    public function getParams()
    {
        return $this->params;
    }

    /**
     * @return boolean
     */
    public function getIsOk()
    {
        return $this->isOk;
    }

    /**
     * returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, returns 0.
     * Warning This function may return Boolean FALSE, but may also return a non-Boolean value which evaluates to FALSE.
     * Please read the section on Booleans for more information. Use the === operator for testing the return value of this function.
     *
     * @return bool|int
     */
    public function rowCount()
    {
        if (!$this->isOk) { // $this->isOk == false(失败), return false(失败); $this->isOk == 0(0行), return 0(0行);
            return $this->isOk;
        }
        // $this->isOk == true || $this->isOk > 0
        if ($this->pdoStatement) {
            return $this->pdoStatement->rowCount();
        } else {
            if (is_numeric($this->isOk)) {
                return $this->isOk;
            } else {
                return 0;
            }
        }
    }

    /**
     * Returns an array containing all of the result set rows
     * Warning This function may return Boolean FALSE, but may also return a non-Boolean value which evaluates to FALSE.
     * Please read the section on Booleans for more information. Use the === operator for testing the return value of this function.
     *
     * @return array|bool
     */
    public function getAll()
    {
        static $getAllResult;

        if (!$this->getAllHasCalled) {
            $this->getAllHasCalled = true;
            if ($this->isOk) {
                if ($this->pdoStatement) {
                    $getAllResult = $this->pdoStatement->fetchAll(self::FETCH_ASSOC);
                } else {
                    $getAllResult = array();
                }
            } else {
                $getAllResult = $this->isOk;
            }
        }

        return $getAllResult;
    }

    /**
     * Returns the first row  of the result set
     * Warning This function may return Boolean FALSE, but may also return a non-Boolean value which evaluates to FALSE.
     * Please read the section on Booleans for more information. Use the === operator for testing the return value of this function.
     *
     * @return array|bool
     */
    public function getFirstRow()
    {
        $allResult = $this->getAll();
        if (empty($allResult)) {
            return $allResult;
        }

        return $allResult[0];
    }

    /**
     * Returns the first row first column of the result
     * Warning This function may return Boolean FALSE, but may also return a non-Boolean value which evaluates to FALSE.
     * Please read the section on Booleans for more information. Use the === operator for testing the return value of this function.
     *
     * @return bool|null
     */
    public function getFirstRowFirstColumn()
    {
        $allResult = $this->getAll();
        if ($allResult) {
            return array_values($allResult[0])[0];
        } else {
            if ($allResult === false) {
                return false;
            } else {
                return null;
            }
        }
    }

    private function clearHistory()
    {
        $this->sql = null;
        $this->params = null;
        $this->isOk = true;
        $this->pdoStatement = null;
        $this->getAllHasCalled = false;
    }

    /**
     * @param string $sql
     * @param null|array $params
     */
    private function execSql($sql, $params)
    {
        if (empty($sql)) {
            return;
        }

        if (empty($params)) { // public function bool PDOStatement::execute (array $input_parameters = null)
            $params = null;
        }

        $this->sql = $sql;
        $this->params = $params;

        if ($this->preExecuteSqlCallback) {
            call_user_func($this->preExecuteSqlCallback, $sql, $params);
        }
        if (!$this->dryRun) {
            $this->pdoStatement = $this->prepare($sql);
            $this->isOk = $this->pdoStatement->execute($params);
        }
    }

    /**
     * @param string $table - 表名
     * @param array $data - ['col1'=>val1, 'col2'=>val2, ...]
     * @return $this
     * @throws \Exception
     */
    public function insert($table, $data)
    {
        $this->clearHistory();

        $result = $this->buildInsert($table, $data);
        if (empty($result)) {
            return $this;
        }
        $sql = $result['sql'];
        $values = $result['values'];

        $this->execSql($sql, $values);

        return $this;
    }

    /**
     * @param string $table - 表名
     * @param array $data - ['col1'=>val1, 'col2'=>val2, ...]
     * @return array|null - ['sql'=>'sql', 'values'=>[...]]
     * @throws \Exception
     */
    private function buildInsert($table, $data)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }
        if (empty($data)) {
            return null;
        }

        if (!is_array($data)) {
            throw new \Exception('参数传递错误');
        }

        $columns = array();
        $values = array();
        foreach ($data as $column => $value) {
            $column = trim(strval($column));
            if (empty($column)) {
                throw new \Exception('参数传递错误');
            }
            $columns[] = $column;
            $values[] = $value;
        }

        $sql = "INSERT INTO {$table} (" . implode(',', $columns) . ") VALUES (" . implode(',', array_fill(0, count($values), '?')) . ")";

        return array('sql' => $sql, 'values' => $values);
    }

    /**
     * @param string $table - 表名
     * @param string[] $columns - ['col1', 'col2', ...]
     * @param array $data - object array, [[val11, val12, ...], [val21, val22, ...], ...]
     * @return $this
     * @throws \Exception
     */
    public function insertMultiValue($table, $columns, $data)
    {
        $this->clearHistory();

        $result = $this->buildInsertMultiValue($table, $columns, $data);
        if (empty($result)) {
            return $this;
        }
        $sql = $result['sql'];
        $values = $result['values'];

        $this->execSql($sql, $values);

        return $this;
    }

    /**
     * @param string $table - 表名
     * @param string[] $columns - ['col1', 'col2', ...]
     * @param array $data - object array, [[val11, val12, ...], [val21, val22, ...], ...]
     * @return array|null - ['sql'=>'sql', 'values'=>[...]]
     * @throws \Exception
     */
    private function buildInsertMultiValue($table, $columns, $data)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }
        if (empty($columns)) {
            return null;
        }
        if (empty($data)) {
            return null;
        }

        if (!is_array($columns)) {
            throw new \Exception('参数传递错误');
        }
        if (!is_array($data)) {
            throw new \Exception('参数传递错误');
        }

        $columns = array_map(function ($column) {
            return trim(strval($column));
        }, $columns);
        $columnsCnt = count($columns);
        $dataTuple = '(' . implode(',', array_fill(0, $columnsCnt, '?')) . ')';

        $values = array();
        $sql = "INSERT INTO {$table} (" . implode(',', $columns) . ')';
        $oxo = ' VALUES ';
        foreach ($data as $line) {
            if (empty($line) || !is_array($line) || count($line) < $columnsCnt) {
                throw new \Exception('参数传递错误');
            }
            $sql .= "{$oxo}{$dataTuple}";

            $line = array_values($line);
            for ($i = 0; $i < $columnsCnt; $i++) {
                $values[] = $line[$i];
            }
            $oxo = ', ';
        }

        return array('sql' => $sql, 'values' => $values);
    }

    /**
     * @param string $table - 表名
     * @param array $data - ['col1'=>val1, 'col2'=>val2, ...]
     * @param string[] $uniqueKeys - ['col1', 'col2', ...] , 导致 INSERT 冲突的 UNIQUE KEY, UPDATE 时不更新这些字段
     * @return $this
     * @throws \Exception
     */
    public function insertOrUpdate($table, $data, $uniqueKeys)
    {
        $this->clearHistory();

        if (empty($uniqueKeys)) {
            return $this;
        }
        if (!is_array($uniqueKeys)) {
            throw new \Exception('参数传递错误');
        }
        $uniqueKeys = array_map(function ($item) {
            return trim(strval($item));
        }, $uniqueKeys);

        $result = $this->buildInsert($table, $data);
        if (empty($result)) {
            return $this;
        }
        $sql = trim($result['sql']);
        $values = $result['values'];

        $oxo = ' ON DUPLICATE KEY UPDATE ';
        foreach ($data as $column => $value) {
            $column = trim(strval($column));
            if (in_array($column, $uniqueKeys)) {
                continue;
            }
            $sql .= "{$oxo}{$column} = VALUES({$column})";
            $oxo = ', ';
        }

        $this->execSql($sql, $values);

        return $this;
    }

    /**
     * @param string $table - 表名
     * @param string[] $columns - ['col1', 'col2', ...]
     * @param string[] $uniqueKeys - ['col1', 'col2', ...] , 导致 INSERT 冲突的 UNIQUE KEY, UPDATE 时不更新这些字段
     * @param array $data - object array, [[val11, val12, ...], [val21, val22, ...], ...]
     * @return $this
     * @throws \Exception
     */
    public function insertMultiValueOrUpdate($table, $columns, $uniqueKeys, $data)
    {
        $this->clearHistory();

        if (empty($uniqueKeys)) {
            return $this;
        }
        if (!is_array($uniqueKeys)) {
            throw new \Exception('参数传递错误');
        }
        $uniqueKeys = array_map(function ($item) {
            return trim(strval($item));
        }, $uniqueKeys);

        $result = $this->buildInsertMultiValue($table, $columns, $data);
        if (empty($result)) {
            return $this;
        }
        $sql = trim($result['sql']);
        $values = $result['values'];

        $oxo = ' ON DUPLICATE KEY UPDATE ';
        foreach ($columns as $column) {
            $column = trim(strval($column));
            if (in_array($column, $uniqueKeys)) {
                continue;
            }
            $sql .= "{$oxo}{$column} = VALUES({$column})";
            $oxo = ', ';
        }

        $this->execSql($sql, $values);

        return $this;
    }

    /**
     * @param string $table - 表名
     * @param null|array $where
     * @param null|array $orderBy - ['col1'=>'desc', 'col2'=>'asc', ...]
     * @param int $limit
     * @return $this
     * @throws \Exception
     */
    public function delete($table, $where = null, $orderBy = null, $limit = 0)
    {
        $this->clearHistory();

        $table = trim(strval($table));
        if (empty($table)) {
            return $this;
        }

        $sql = "DELETE FROM {$table}";
        $params = null;
        if ($where) {
            $result = $this->buildWhere($where);
            if ($result) {
                $whereSql = trim($result['where']);
                if ($whereSql) {
                    $sql .= " WHERE {$whereSql}";
                }
                $params = $result['params'];
            }
        }

        if ($orderBy) {
            if (!is_array($orderBy)) {
                throw new \Exception('参数传递错误');
            }
            $oxo = ' ORDER BY ';
            foreach ($orderBy as $column => $order) {
                $column = trim(strval($column));
                if (empty($column)) {
                    throw new \Exception('参数传递错误');
                }
                $order = strtoupper(trim(strval($order)));
                if (!($order == 'ASC' || $order == 'DESC')) {
                    throw new \Exception('参数传递错误');
                }
                $sql .= "{$oxo}{$column} {$order}";
                $oxo = ', ';
            }
        }

        $limit = intval($limit);
        if ($limit) {
            $sql .= " LIMIT {$limit}";
        }

        $this->execSql($sql, $params);

        return $this;
    }

    /**
     * @param string $table - 表名
     * @param $data - ['col1'=>val1, 'col2'=>val2, ...]
     * @param null|array $where
     * @param null|array $orderBy - ['col1'=>'desc', 'col2'=>'asc', ...]
     * @param int $limit
     * @return $this
     * @throws \Exception
     */
    public function update($table, $data, $where = null, $orderBy = null, $limit = 0)
    {
        $this->clearHistory();

        $table = trim(strval($table));
        if (empty($table)) {
            return $this;
        }
        if (empty($data)) {
            return $this;
        }
        if (!is_array($data)) {
            throw new \Exception('参数传递错误');
        }

        $sql = "UPDATE {$table}";
        $params = array();

        $oxo = ' SET ';
        foreach ($data as $column => $value) {
            $column = trim(strval($column));
            if (empty($column)) {
                throw new \Exception('参数传递错误');
            }
            if (isset($value)) {
                $sql .= "{$oxo}{$column} = ?";
                $params[] = $value;
            } else {
                $sql .= "{$oxo}{$column} = NULL";
            }
            $oxo = ', ';
        }

        if ($where) {
            $result = $this->buildWhere($where);
            if ($result) {
                $whereSql = trim($result['where']);
                if ($whereSql) {
                    $sql .= " WHERE {$whereSql}";
                }
                if ($result['params']) {
                    $params = array_merge($params, $result['params']);
                }
            }
        }

        if ($orderBy) {
            if (!is_array($orderBy)) {
                throw new \Exception('参数传递错误');
            }
            $oxo = ' ORDER BY ';
            foreach ($orderBy as $column => $order) {
                $column = trim(strval($column));
                if (empty($column)) {
                    throw new \Exception('参数传递错误');
                }
                $order = strtoupper(trim(strval($order)));
                if (!($order == 'ASC' || $order == 'DESC')) {
                    throw new \Exception('参数传递错误');
                }
                $sql .= "{$oxo}{$column} {$order}";
                $oxo = ', ';
            }
        }

        $limit = intval($limit);
        if ($limit) {
            $sql .= " LIMIT {$limit}";
        }

        $this->execSql($sql, $params);

        return $this;
    }

    /**
     * @param string $table - 表名
     * @param string|string[] $columns - '*' 或者 'col1,col2,...' 或者 ['col1', 'col2', ...]
     * @param null|array $where
     * @param null|string|string[] $groupBy - 'col1,col2,...' 或者 ['col1', 'col2', ...]
     * @param null|array $orderBy - ['col1'=>'desc', 'col2'=>'asc', ...]
     * @param int $limit
     * @param int $offset
     * @return $this
     * @throws \Exception
     */
    public function select($table, $columns, $where = null, $groupBy = null, $orderBy = null, $limit = 0, $offset = 0)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return $this;
        }

        if (empty($columns)) {
            throw new \Exception('参数传递错误');
        }

        if (is_string($columns)) {
            $columns = trim($columns);
            if (empty($columns)) {
                throw new \Exception('参数传递错误');
            }
            $sql = "SELECT {$columns} FROM {$table}";
        } elseif (is_array($columns)) {
            $sql = 'SELECT ' . implode(',', array_map(function ($column) {
                    return trim(strval($column));
                }, $columns)) . " FROM {$table}";
        } else {
            throw new \Exception('参数传递错误');
        }

        $params = array();

        if ($where) {
            $result = $this->buildWhere($where);
            if ($result) {
                $whereSql = trim($result['where']);
                if ($whereSql) {
                    $sql .= " WHERE {$whereSql}";
                }
                if ($result['params']) {
                    $params = array_merge($params, $result['params']);
                }
            }
        }

        if ($groupBy) {
            if (is_string($groupBy)) {
                $groupBy = trim($groupBy);
                if (empty($groupBy)) {
                    throw new \Exception('参数传递错误');
                }
                $sql .= " GROUP BY {$groupBy}";
            } elseif (is_array($groupBy)) {
                $sql .= ' GROUP BY ' . implode(',', array_map(function ($column) {
                        return trim(strval($column));
                    }, $groupBy));
            } else {
                throw new \Exception('参数传递错误');
            }
        }

        if ($orderBy) {
            if (!is_array($orderBy)) {
                throw new \Exception('参数传递错误');
            }
            $oxo = ' ORDER BY ';
            foreach ($orderBy as $column => $order) {
                $column = trim(strval($column));
                if (empty($column)) {
                    throw new \Exception('参数传递错误');
                }
                $order = strtoupper(trim(strval($order)));
                if (!($order == 'ASC' || $order == 'DESC')) {
                    throw new \Exception('参数传递错误');
                }
                $sql .= "{$oxo}{$column} {$order}";
                $oxo = ', ';
            }
        }

        $limit = intval($limit);
        if ($limit) {
            $sql .= " LIMIT {$limit} OFFSET " . intval($offset);
        }

        $this->execSql($sql, $params);

        return $this;
    }

    /**
     * @param array $where
     * @param string $logicalOp - refer self::LOGICAL_AND | self::LOGICAL_OR
     * @return array|null - ['where'=>'whereSql', 'params'=>[...]]
     * @throws \Exception
     */
    private function buildWhere($where, $logicalOp = self::LOGICAL_AND)
    {
        if (empty($where)) {
            return null;
        }
        if (!is_array($where)) {
            throw new \Exception('参数传递错误');
        }

        $whereSql = null;
        $params = array();
        if ($logicalOp == self::LOGICAL_AND) {
            $oxo = ' AND ';
        } elseif ($logicalOp == self::LOGICAL_OR) {
            $oxo = ' OR ';
        } else {
            throw new \Exception('参数传递错误');
        }

        foreach ($where as $name => $value) {
            if ($name == self::LOGICAL_AND || $name == self::LOGICAL_OR) {
                $result = $this->buildWhere($value, $name);
                $subWhereSql = empty($result['where']) ? null : trim($result['where']);
                $subParams = empty($result['params']) ? array() : $result['params'];
                if ($subWhereSql) {
                    if ($name == self::LOGICAL_AND) {
                        if (empty($whereSql)) {
                            $whereSql = $subWhereSql;
                            $params = $subParams;
                        } else {
                            $whereSql .= "{$oxo}{$subWhereSql}";
                            $params = array_merge($params, $subParams);
                        }
                    } else {
                        if (empty($whereSql)) {
                            $whereSql = "({$subWhereSql})";
                            $params = $subParams;
                        } else {
                            $whereSql .= "{$oxo}({$subWhereSql})";
                            $params = array_merge($params, $subParams);
                        }
                    }
                }
            } else {
                if (is_array($value)) {
                    foreach ($value as $op => $val) {
                        $op = strtoupper(trim(strval($op)));
                        if ($op == 'LIKE'
                            || $op == 'NOT LIKE'
                            || $op == 'REGEXP'
                            || $op == 'NOT REGEXP'
                        ) {
                            $val = strval($val);
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} ?";
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} ?";
                            }
                            $params[] = $val;
                        } elseif ($op == 'BETWEEN' || $op == 'NOT BETWEEN') {
                            if (!is_array($val)) {
                                throw new \Exception('参数传递错误');
                            }
                            $val = array_values($val);
                            if (count($val) < 2) {
                                throw new \Exception('参数传递错误');
                            }
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} ? AND ?";
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} ? AND ?";
                            }
                            $params[] = $val[0];
                            $params[] = $val[1];
                        } elseif ($op == 'IN' || $op == 'NOT IN') {
                            if (!is_array($val)) {
                                throw new \Exception('参数传递错误');
                            }
                            $val = array_values($val);
                            $cnt = count($val);
                            if ($cnt < 1) {
                                throw new \Exception('参数传递错误');
                            }
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} (" . implode(',', array_fill(0, $cnt, '?')) . ')';
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} (" . implode(',', array_fill(0, $cnt, '?')) . ')';
                            }
                            $params = array_merge($params, $val);
                        } else {
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} ?";
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} ?";
                            }
                            $params[] = $val;
                        }
                    }
                } else {
                    $valueStr = strtoupper(trim(strval($value)));
                    if ($valueStr == 'IS NULL') {
                        if (empty($whereSql)) {
                            $whereSql = "{$name} {$valueStr}";
                        } else {
                            $whereSql .= "{$oxo}{$name} {$valueStr}";
                        }
                    } elseif ($valueStr == 'IS NOT NULL') {
                        if (empty($whereSql)) {
                            $whereSql = "{$name} {$valueStr}";
                        } else {
                            $whereSql .= "{$oxo}{$name} {$valueStr}";
                        }
                    } else {
                        if (empty($whereSql)) {
                            $whereSql = "{$name} = ?";
                            $params[] = $value;
                        } else {
                            $whereSql .= "{$oxo}{$name} = ?";
                            $params[] = $value;
                        }
                    }
                }
            }
        }

        return array('where' => $whereSql, 'params' => $params);
    }

}